# 导出excel数据
import hashlib
import os
import time

import xlrd
import xlwt
from django.conf import settings
from utils.common import getfulldomian


def len_byte(value):
    # 获取字符串长度，一个中文的长度为2
    length = len(value)
    utf8_length = len(value.encode('utf-8'))
    length = (utf8_length - length) / 2 + length
    return int(length)


def export_excel(request,field_data: list, data: list, FileName: str, file_path: str = settings.MEDIA_ROOT):
    """
    Excel导出
    :param request: 请求request
    :param data: 数据源
    :param field_data: 首行数据源（表头）
    :param file_path: 文件保存路径（默认保存在media路径）
    :param FileName: 文件保存名字
    :return:返回文件的下载url完整路径
    """
    wbk = xlwt.Workbook(encoding='utf-8')
    sheet = wbk.add_sheet('Sheet1', cell_overwrite_ok=True)  # 第二参数用于确认同一个cell单元是否可以重设值。
    style = xlwt.XFStyle()  # 赋值style为XFStyle()，初始化样式
    # 设置居中
    wbk.set_colour_RGB(0x23, 0, 60, 139)
    xlwt.add_palette_colour("custom_colour_35", 0x23)
    tab_al = xlwt.Alignment()
    tab_al.horz = 0x02  # 设置水平居中
    tab_al.vert = 0x01  # 设置垂直居中
    # 设置表头单元格背景颜色
    tab_pattern = xlwt.Pattern()  # 创建一个模式
    tab_pattern.pattern = xlwt.Pattern.SOLID_PATTERN  # 设置其模式为实型
    tab_pattern.pattern_fore_colour = 55
    # 设置单元格内字体样式
    tab_fnt = xlwt.Font()  # 创建一个文本格式，包括字体、字号和颜色样式特性
    tab_fnt.height = 200
    default_width = 14
    tab_fnt.name = u'楷体'  # 设置其字体为微软雅黑
    tab_fnt.colour_index = 1  # 设置其字体颜色
    # 设置单元格下框线样式
    tab_borders = xlwt.Borders()
    tab_borders.left = xlwt.Borders.THIN
    tab_borders.right = xlwt.Borders.THIN
    tab_borders.top = xlwt.Borders.THIN
    tab_borders.bottom = xlwt.Borders.THIN
    tab_borders.left_colour = 23
    tab_borders.right_colour = 23
    tab_borders.bottom_colour = 23
    tab_borders.top_colour = 23
    #### 把数据写入excel中
    # 所有表格单元格样式
    # 先生成表头
    style.alignment = tab_al  # 设置居中
    style.pattern = tab_pattern  # 设置表头单元格背景颜色
    style.font = tab_fnt  # 设置单元格内字体样式
    style.borders = tab_borders
    for index, ele in enumerate(field_data):
        sheet.write_merge(0, 0, index, index, ele, style)  # (列开始, 列结束, 行开始, 行结束, '数据内容')

    # 确定栏位宽度
    col_width = []
    for index, ele in enumerate(data):
        for inx, values in enumerate(ele.values()):
            if index == 0:
                col_width.append(len_byte(str(values)))
            else:
                if col_width[inx] < len_byte(str(values)):
                    col_width[inx] = len_byte(str(values))
    # 设置栏位宽度，栏位宽度小于10时候采用默认宽度
    for i in range(len(col_width)):
        if col_width[i] > 10:
            width = col_width[i] if col_width[i] < 36 else 36
            sheet.col(i).width = 256 * (width + 6)
        else:
            sheet.col(i).width = 256 * (default_width)

    row = 1
    # 内容背景颜色
    left_pattern = xlwt.Pattern()  # 创建一个模式
    left_pattern.pattern = xlwt.Pattern.SOLID_PATTERN  # 设置其模式为实型
    left_pattern.pattern_fore_colour = 1

    # 设置单元格内字体样式
    left_fnt = xlwt.Font()  # 创建一个文本格式，包括字体、字号和颜色样式特性
    left_fnt.height = 200
    left_fnt.name = u'楷体'  # 设置其字体为微软雅黑
    left_fnt.colour_index = 0  # 设置其字体颜色

    left_style = style
    left_style.pattern = left_pattern
    left_style.font = left_fnt

    for results in data:
        for index, values in enumerate(results.values()):
            sheet.write(row, index, label=values, style=left_style)
        row += 1

    monthTime = time.strftime('%Y-%m-%d', time.localtime(time.time()))
    pathRoot = os.path.join(file_path, 'systemexport', monthTime)
    if not os.path.exists(pathRoot):
        os.makedirs(pathRoot)
    path_name = os.path.join(pathRoot, FileName)
    wbk.save(path_name)
    return getfulldomian(request) + settings.MEDIA_URL + 'systemexport' + "/" + monthTime + "/" + FileName
    # return os.path.join('system', monthTime, FileName)



def excel_to_data(file_url, field_data):
    """
    读取导入的excel文件
    :param request:
    :param field_data: 首行数据源
    :param data: 数据源
    :param FilName: 文件名
    :return:
    """
    # 读取excel 文件
    data = xlrd.open_workbook(os.path.join(settings.BASE_DIR.replace('\\', os.sep), *file_url.split(os.sep)))
    table = data.sheets()[0]
    # 创建一个空列表，存储Excel的数据
    tables = []
    for i, rown in enumerate(range(table.nrows)):
        if i == 0: continue
        array = {}
        for index, ele in enumerate(field_data.keys()):
            cell_value = table.cell_value(rown, index)
            # 由于excel导入数字类型后，会出现数字加 .0 的，进行处理
            if type(cell_value) is float and str(cell_value).split('.')[1] == '0':
                cell_value = int(str(cell_value).split('.')[0])
            if type(cell_value) is str:
                cell_value = cell_value.strip(' \t\n\r')
            array[ele] = cell_value

        tables.append(array)
    return tables

def export_sensor(data: list,FileName: str):
        from openpyxl import Workbook
        from openpyxl.styles import PatternFill, Border, Side,Font,Alignment
        # 初始化
        wb = Workbook()
        # 创建第一个sheet
        sheet = wb.active
        # 创建一个无边框的Border对象
        no_border = Border(left=Side(), right=Side(), top=Side(), bottom=Side())
        # 应用无边框样式到工作表的每一个单元格
        for row in sheet.iter_rows():
            for cell in row:
                cell.border = no_border
        # 设置单元格字体
        font = Font(size=20, bold=True, italic=False, color='000000', name='宋体')
        align = Alignment(horizontal='center', vertical='center')
        align1 = Alignment(horizontal='right', vertical='center')
        # 创建一个填充样式，设置背景颜色和边框
        fillh1 = PatternFill(start_color='FFC6EFCE', end_color='FFC6EFCE', fill_type='solid')
        fillh2 = PatternFill(start_color='FFCBFFFF', end_color='FFCBFFFF', fill_type='solid')
        fillh3 = PatternFill(start_color='FFFFCC99', end_color='FFFFCC99', fill_type='solid')
        borderh2 = Border(left=Side(border_style='thin'),right=Side(border_style='thin'),top=Side(border_style='thin'),bottom=Side(border_style='thin'))
        fill = PatternFill(start_color='FFFFFFFF', end_color='FFFFFFFF', fill_type='solid')
        fill1 = PatternFill(start_color='FFD9D9D9', end_color='FFD9D9D9', fill_type='solid')
        border = Border(left=Side(border_style='thin'),right=Side(border_style='thin'),top=Side(border_style='thin'),bottom=Side(border_style='thin'))
        height=20*1.058604651163

        sheet.column_dimensions['A'].width = (5.25*1.058604651163)
        sheet.column_dimensions['B'].width = (16.00*1.058604651163)
        sheet.column_dimensions['C'].width = (23.12*1.058604651163)
        sheet.column_dimensions['D'].width = (5.50*1.058604651163)
        sheet.column_dimensions['E'].width = (5.50*1.058604651163)
        sheet.column_dimensions['F'].width = (14.25*1.058604651163)
        sheet.column_dimensions['G'].width = (14.25*1.058604651163)
        sheet.column_dimensions['H'].width = (17.12*1.058604651163)
        sheet.column_dimensions['I'].width = (14.88*1.058604651163)
        # 在第1行第1列的单元格写入111
        index=5#空5行

        index=index+1
        sheet.merge_cells('A'+str(index)+":"+'I'+str(index))
        sheet['A'+str(index)].border = Border(left=Side(border_style='thin'),right=Side(),top=Side(border_style='thin'),bottom=Side(border_style='thin'))
        sheet['B'+str(index)].border = Border(left=Side(),right=Side(),top=Side(border_style='thin'),bottom=Side(border_style='thin'))
        sheet['C'+str(index)].border = Border(left=Side(),right=Side(),top=Side(border_style='thin'),bottom=Side(border_style='thin'))
        sheet['D'+str(index)].border = Border(left=Side(),right=Side(),top=Side(border_style='thin'),bottom=Side(border_style='thin'))
        sheet['E'+str(index)].border = Border(left=Side(),right=Side(),top=Side(border_style='thin'),bottom=Side(border_style='thin'))
        sheet['F'+str(index)].border = Border(left=Side(),right=Side(),top=Side(border_style='thin'),bottom=Side(border_style='thin'))
        sheet['G'+str(index)].border = Border(left=Side(),right=Side(),top=Side(border_style='thin'),bottom=Side(border_style='thin'))
        sheet['H'+str(index)].border = Border(left=Side(),right=Side(),top=Side(border_style='thin'),bottom=Side(border_style='thin'))
        sheet['I'+str(index)].border = Border(left=Side(),right=Side(border_style='thin'),top=Side(border_style='thin'),bottom=Side(border_style='thin'))

        sheet['A'+str(index)].fill = fillh1
        sheet['B'+str(index)].fill = fillh1
        sheet['C'+str(index)].fill = fillh1
        sheet['D'+str(index)].fill = fillh1
        sheet['E'+str(index)].fill = fillh1
        sheet['F'+str(index)].fill = fillh1
        sheet['G'+str(index)].fill = fillh1
        sheet['H'+str(index)].fill = fillh1
        sheet['I'+str(index)].fill = fillh1

        sheet['C'+str(index)].alignment  = align1
        sheet['G'+str(index)].alignment  = align1

        sheet['A'+str(index)] = "新增"

        for rows in data:
            index=index+1
            sheet.merge_cells('D'+str(index)+":"+'F'+str(index))
            sheet['A'+str(index)].border = Border(left=Side(border_style='thin'),right=Side(),top=Side(border_style='thin'),bottom=Side(border_style='thin'))
            sheet['B'+str(index)].border = Border(left=Side(),right=Side(),top=Side(border_style='thin'),bottom=Side(border_style='thin'))
            sheet['C'+str(index)].border = Border(left=Side(),right=Side(),top=Side(border_style='thin'),bottom=Side(border_style='thin'))
            sheet['D'+str(index)].border = Border(left=Side(),right=Side(),top=Side(border_style='thin'),bottom=Side(border_style='thin'))
            sheet['E'+str(index)].border = Border(left=Side(),right=Side(),top=Side(border_style='thin'),bottom=Side(border_style='thin'))
            sheet['F'+str(index)].border = Border(left=Side(),right=Side(),top=Side(border_style='thin'),bottom=Side(border_style='thin'))
            sheet['G'+str(index)].border = Border(left=Side(),right=Side(),top=Side(border_style='thin'),bottom=Side(border_style='thin'))
            sheet['H'+str(index)].border = Border(left=Side(),right=Side(),top=Side(border_style='thin'),bottom=Side(border_style='thin'))
            sheet['I'+str(index)].border = Border(left=Side(),right=Side(border_style='thin'),top=Side(border_style='thin'),bottom=Side(border_style='thin'))

            sheet['A'+str(index)].fill = fillh2
            sheet['B'+str(index)].fill = fillh2
            sheet['C'+str(index)].fill = fillh2
            sheet['D'+str(index)].fill = fillh2
            sheet['E'+str(index)].fill = fillh2
            sheet['F'+str(index)].fill = fillh2
            sheet['G'+str(index)].fill = fillh2
            sheet['H'+str(index)].fill = fillh2
            sheet['I'+str(index)].fill = fillh2

            sheet['C'+str(index)].alignment  = align1
            sheet['G'+str(index)].alignment  = align1

            sheet['A'+str(index)] = rows["id"]
            sheet['B'+str(index)] ="柜号："+ rows["name"]
            sheet['C'+str(index)] ="型号："+ rows["sn"]
            sheet['G'+str(index)] ="名称:"
            sheet['H'+str(index)] =rows["name"]
            
            index=index+1
            sheet['A'+str(index)].border = border
            sheet['B'+str(index)].border = border
            sheet['C'+str(index)].border = border
            sheet['D'+str(index)].border = border
            sheet['E'+str(index)].border = border
            sheet['F'+str(index)].border = border
            sheet['G'+str(index)].border = border
            sheet['H'+str(index)].border = border
            sheet['I'+str(index)].border = border

            sheet['A'+str(index)].fill = fill1
            sheet['B'+str(index)].fill = fill1
            sheet['C'+str(index)].fill = fill1
            sheet['D'+str(index)].fill = fill1
            sheet['E'+str(index)].fill = fill1
            sheet['F'+str(index)].fill = fill1
            sheet['G'+str(index)].fill = fill1
            sheet['H'+str(index)].fill = fill1
            sheet['I'+str(index)].fill = fill1


            sheet['A'+str(index)] = "序号"
            sheet['B'+str(index)] = "元件名称"
            sheet['C'+str(index)] = "型号规格"
            sheet['D'+str(index)] = "单位"
            sheet['E'+str(index)] = "数量"
            sheet['F'+str(index)] = "单   价"
            sheet['G'+str(index)] = "总   价"
            sheet['G'+str(index)] = "生产厂家"
            sheet['I'+str(index)] = "备注"

            for row in rows["sensor"]:
                index=index+1
                sheet['A'+str(index)].border = border
                sheet['B'+str(index)].border = border
                sheet['C'+str(index)].border = border
                sheet['D'+str(index)].border = border
                sheet['E'+str(index)].border = border
                sheet['F'+str(index)].border = border
                sheet['G'+str(index)].border = border
                sheet['H'+str(index)].border = border
                sheet['I'+str(index)].border = border
                
                sheet['A'+str(index)].alignment  = align
                sheet['D'+str(index)].alignment  = align
                sheet['E'+str(index)].alignment  = align
                sheet['F'+str(index)].alignment  = align
                sheet['G'+str(index)].alignment  = align

                sheet['A'+str(index)] = row["id"]
                sheet['B'+str(index)] = row["name"]
                sheet['C'+str(index)] = row["sn"]
                sheet['D'+str(index)] = row["unit"]
                sheet['E'+str(index)] = row["nums"]
                sheet['F'+str(index)] = row["price"]
                sheet['G'+str(index)] = 0
                sheet['G'+str(index)] = row["brand"]
                sheet['I'+str(index)] = row["remark"]


            index=index+1
            sheet['A'+str(index)].border = border
            sheet['B'+str(index)].border = border
            sheet['C'+str(index)].border = border
            sheet['D'+str(index)].border = border
            sheet['E'+str(index)].border = border
            sheet['F'+str(index)].border = border
            sheet['G'+str(index)].border = border
            sheet['H'+str(index)].border = border
            sheet['I'+str(index)].border = border
            
            sheet['A'+str(index)].alignment  = align
            sheet['D'+str(index)].alignment  = align
            sheet['E'+str(index)].alignment  = align
            sheet['F'+str(index)].alignment  = align
            sheet['G'+str(index)].alignment  = align

            sheet['A'+str(index)] = "1"
            sheet['B'+str(index)] = "辅料"
            sheet['C'+str(index)] = ""
            sheet['D'+str(index)] = ""
            sheet['E'+str(index)] = ""
            sheet['F'+str(index)] = ""
            sheet['G'+str(index)] = ""
            sheet['G'+str(index)] = ""
            sheet['I'+str(index)] = ""

            index=index+1
            sheet['A'+str(index)].border = border
            sheet['B'+str(index)].border = border
            sheet['C'+str(index)].border = border
            sheet['D'+str(index)].border = border
            sheet['E'+str(index)].border = border
            sheet['F'+str(index)].border = border
            sheet['G'+str(index)].border = border
            sheet['H'+str(index)].border = border
            sheet['I'+str(index)].border = border
            
            sheet['A'+str(index)].alignment  = align
            sheet['D'+str(index)].alignment  = align
            sheet['E'+str(index)].alignment  = align
            sheet['F'+str(index)].alignment  = align
            sheet['G'+str(index)].alignment  = align

            sheet['A'+str(index)] = "1"
            sheet['B'+str(index)] = "综合费"
            sheet['C'+str(index)] = ""
            sheet['D'+str(index)] = ""
            sheet['E'+str(index)] = ""
            sheet['F'+str(index)] = ""
            sheet['G'+str(index)] = ""
            sheet['G'+str(index)] = ""
            sheet['I'+str(index)] = ""

            index=index+1
            sheet['A'+str(index)].border = border
            sheet['B'+str(index)].border = border
            sheet['C'+str(index)].border = border
            sheet['D'+str(index)].border = border
            sheet['E'+str(index)].border = border
            sheet['F'+str(index)].border = border
            sheet['G'+str(index)].border = border
            sheet['H'+str(index)].border = border
            sheet['I'+str(index)].border = border
            sheet['A'+str(index)].fill = fillh3
            sheet['B'+str(index)].fill = fillh3
            sheet['C'+str(index)].fill = fillh3
            sheet['D'+str(index)].fill = fillh3
            sheet['E'+str(index)].fill = fillh3
            sheet['F'+str(index)].fill = fillh3
            sheet['G'+str(index)].fill = fillh3
            sheet['H'+str(index)].fill = fillh3
            sheet['I'+str(index)].fill = fillh3


            sheet['A'+str(index)].alignment  = align
            sheet['D'+str(index)].alignment  = align
            sheet['E'+str(index)].alignment  = align
            sheet['F'+str(index)].alignment  = align
            sheet['G'+str(index)].alignment  = align

            sheet['A'+str(index)] = "1"
            sheet['B'+str(index)] = "单台合计"
            sheet['C'+str(index)] = ""
            sheet['D'+str(index)] = ""
            sheet['E'+str(index)] = ""
            sheet['F'+str(index)] = ""
            sheet['G'+str(index)] = ""
            sheet['G'+str(index)] = ""
            sheet['I'+str(index)] = ""

            index=index+1
            sheet['A'+str(index)].border = border
            sheet['B'+str(index)].border = border
            sheet['C'+str(index)].border = border
            sheet['D'+str(index)].border = border
            sheet['E'+str(index)].border = border
            sheet['F'+str(index)].border = border
            sheet['G'+str(index)].border = border
            sheet['H'+str(index)].border = border
            sheet['I'+str(index)].border = border
            sheet['A'+str(index)].fill = fillh3
            sheet['B'+str(index)].fill = fillh3
            sheet['C'+str(index)].fill = fillh3
            sheet['D'+str(index)].fill = fillh3
            sheet['E'+str(index)].fill = fillh3
            sheet['F'+str(index)].fill = fillh3
            sheet['G'+str(index)].fill = fillh3
            sheet['H'+str(index)].fill = fillh3
            sheet['I'+str(index)].fill = fillh3

            sheet['A'+str(index)].alignment  = align
            sheet['D'+str(index)].alignment  = align
            sheet['E'+str(index)].alignment  = align
            sheet['F'+str(index)].alignment  = align
            sheet['G'+str(index)].alignment  = align

            sheet['A'+str(index)] = ""
            sheet['B'+str(index)] = "总计"
            sheet['C'+str(index)] = ""
            sheet['D'+str(index)] = ""
            sheet['E'+str(index)] = ""
            sheet['F'+str(index)] = ""
            sheet['G'+str(index)] = ""
            sheet['G'+str(index)] = ""
            sheet['I'+str(index)] = ""
        # 保存本地excel文件中
        wb.save(FileName)
